/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.akro.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.akro.Main;

/**
 *
 * @author Jose Carlos Palma
 * @version v1.0
 */
public class OracleDB {

    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private String user;
    private String pass;
    private String sid;
    private String ip;
    private String port;
    private Connection cnx;
    private String url;
    private String owner;
    private String table;
    private String db;
    private long time;

    public OracleDB(String db) {
        this.db = db;
        this.user = Main.conf.getStringValue("db." + db + ".user");
        this.pass = Main.conf.getStringValue("db." + db + ".pass");
        this.sid = Main.conf.getStringValue("db." + db + ".sid");
        this.ip = Main.conf.getStringValue("db." + db + ".ip");
        this.port = Main.conf.getStringValue("db." + db + ".port");
        this.owner = Main.conf.getStringValue("db." + db + ".inbox.owner");
        this.table = Main.conf.getStringValue("db." + db + ".inbox.table");
        this.time = Main.conf.getLongValue("db." + db + ".inbox.time");


        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(OracleDB.class.getName()).log(Level.SEVERE, null, ex);
            System.exit(1);
        }
    }

    public void connect() {

        if (cnx != null) {
            try {
                cnx.close();
            } catch (SQLException ex) {
                Logger.getLogger(OracleDB.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        this.url = "jdbc:oracle:thin:@" + this.ip + ":" + this.port + ":" + this.sid;

        try {
            System.out.println("");
            this.cnx = DriverManager.getConnection(url, user, pass);
        } catch (SQLException ex) {
            Logger.getLogger(OracleDB.class.getName()).log(Level.SEVERE, null, ex);
            System.exit(1);
        }
        System.out.println("INFO>>> InBox[" + db + "] esta conectado con la base de datos.");
    }

    public void close() {
        if (cnx != null) {
            try {
                cnx.commit();
                cnx.close();
            } catch (SQLException ex) {
                //Logger.getLogger(OracleDB.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        System.out.println("INFO>>> InBox[" + db + "] esta desconectado con la base de datos.");
    }

    public void reconnect(boolean force) {
        try {
            if (!force && cnx != null && !cnx.isClosed()) {
                return;
            }
        } catch (SQLException ex) {
            Logger.getLogger(OracleDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        connect();
    }

    public void readInbox() {
        reconnect(false);
        Statement stmt = null;
        ResultSet res = null;
        try {

            StringBuilder sb = new StringBuilder();
            sb.append("SELECT ID, FECHA, TELEFONO, MENSAJE, ENVIADO, FECHA_ENVIO, COMENTARIO");
            sb.append(" FROM ").append(owner).append(".").append(table);
            sb.append(" WHERE ENVIADO = 'N'");
            stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

            //System.out.println("Count: " + );

        } catch (SQLException ex) {
            try {
                Logger.getLogger(OracleDB.class.getName()).log(Level.SEVERE, null, ex);
                if (stmt != null) {
                    stmt.close();
                }

                if (res != null) {
                    res.close();
                }
            } catch (SQLException ex1) {
            }
        }

    }

    /**
     * @return the user
     */
    public String getUser() {
        return user;
    }

    /**
     * @return the sid
     */
    public String getSid() {
        return sid;
    }

    /**
     * @return the ip
     */
    public String getIp() {
        return ip;
    }

    /**
     * @return the port
     */
    public String getPort() {
        return port;
    }

    /**
     * @return the owner
     */
    public String getOwner() {
        return owner;
    }

    /**
     * @return the table
     */
    public String getTable() {
        return table;
    }

    /**
     * @return the time
     */
    public long getTime() {
        return time;
    }
}
